#include "stusql.h"

stuSql * stuSql::ptrstuSql = nullptr;

stuSql::stuSql(QObject *parent)
    : QObject{parent}
{
    init();

    // StuInfo s;
    // s.id = 1;
    // s.name = "sas";

    //qDebug() <<addStu(s);

    //qDebug() <<getStuCount();

    //qDebug() <<getPageStu(0, 1).value(0).name;

    //qDebug() <<delStu(2);

    //qDebug() <<updateStuInfo(s);


    // userInfo u;
    // u.username = "xxx";
    // u.password = "122";
    // u.aut = "admin";

    //qDebug() <<addUser(u);
    //u.username ="yyy";
    //qDebug() <<addUser(u);

    //auto l = getAllUser();

    //qDebug() <<isExit("xxx");

    //u.password = "111";
    //qDebug() <<updateUser(u);

    //qDebug() <<delUser("xxx");

}


void stuSql::init()
{
    //if(QSqlDatabase::drivers().isEmpty())
    //QMessageBox::information(nullptr, tr("缺少驱动"), tr("请安装驱动"));
    //QString str = "C:/Users/ZY/Desktop/stu/data.db";
    m_db = QSqlDatabase::addDatabase("QSQLITE");

#if 1
    auto str =  QCoreApplication::applicationDirPath() + "/data.db";
    qDebug()<<str;
#endif

    m_db.setDatabaseName(str);
    // m_db.setHostName("localhost");
    // m_db.setUserName("root");
    // m_db.setPassword("123456");

    if(!m_db.open())
    {
        qDebug()<<"提示未打开数据库";
    }
}

quint32 stuSql::getStuCount()
{
    QSqlQuery sql(m_db);

    sql.exec("select count(id) from student;");
    quint32 uiCount = 0;

    while(sql.next())
    {
        uiCount = sql.value(0).toUInt();
    }

    //qDebug()<<uiCount;

    return uiCount;
}

QList<StuInfo> stuSql::getPageStu(quint32 page, quint32 uicount)
{
    QList<StuInfo> l;
    QSqlQuery sql(m_db);

    QString strsql = QString("select * from student order by id limit %1 offset %2").
                     arg(uicount).
                     arg(page * uicount);

    sql.exec(strsql);

    StuInfo info;

    while(sql.next())
    {
        info.id = sql.value(0).toUInt();
        info.name = sql.value(1).toString();
        info.age = sql.value(2).toUInt();
        info.grade = sql.value(3).toUInt();
        info.uiclass = sql.value(4).toUInt();
        info.stuid = sql.value(5).toUInt();
        info.phone = sql.value(6).toString();
        info.wechat = sql.value(7).toString();

        l.push_back(info);
    }

    //qDebug()<<sql.size();

    return l;

}

bool stuSql::addStu(StuInfo info)
{
    QSqlQuery sql(m_db);

    QString strsql = QString("insert into student values(null, '%1', %2, %3, %4, %5, '%6', '%7')").
                             arg(info.name).
                             arg(info.age).
                             arg(info.grade).
                             arg(info.uiclass).
                             arg(info.stuid).
                             arg(info.phone).
                             arg(info.wechat);

    //qDebug() << sql.exec(strsql);
    return sql.exec(strsql);
}

bool stuSql::addStu(QList<StuInfo> l)
{
    QSqlQuery sql(m_db);

    for(auto info:l)
    {
        QString strsql = QString("insert into student values(null, '%1', %2, %3, %4, %5, '%6', '%7')").
                         arg(info.name).
                         arg(info.age).
                         arg(info.grade).
                         arg(info.uiclass).
                         arg(info.stuid).
                         arg(info.phone).
                         arg(info.wechat);
        sql.exec(strsql);
    }

    m_db.commit();
    return true;
}

bool stuSql::delStu(int id)
{
    QSqlQuery sql(m_db);

    return sql.exec(QString("delete from student where id = %1").arg(id));

}

void stuSql::clearStuTable()
{
    QSqlQuery sql(m_db);

    sql.exec("delete from student");
    sql.exec("UPDATE sqlite_sequence set seq=0 where name='student'");
}

bool stuSql::updateStuInfo(StuInfo info)
{
    QSqlQuery sql(m_db);

    QString strsql = QString("update student set name='%1',age=%2,grade=%3,class=%4,stuid=%5,"
                             "phone='%6',wechat='%7' where id = %8").
                     arg(info.name).
                     arg(info.age).
                     arg(info.grade).
                     arg(info.uiclass).
                     arg(info.stuid).
                     arg(info.phone).
                     arg(info.wechat).
                     arg(info.id);

    qDebug()<<strsql;
    return sql.exec(strsql);
}

QList<userInfo> stuSql::getAllUser()
{
    QList<userInfo> l;
    QSqlQuery sql(m_db);

    sql.exec("select * from username");

    userInfo info;

    while (sql.next()) {
        info.username = sql.value(0).toString();
        info.password = sql.value(1).toString();
        info.aut = sql.value(2).toString();
        l.push_back(info);
    }
    return l;
}

bool stuSql::isExit(QString strUser)
{
    QSqlQuery sql(m_db);

    sql.exec(QString("select * from username where username='%1';").arg(strUser));

    //bool ret = false;

    return sql.next();
}

bool stuSql::updateUser(userInfo info)
{
    QSqlQuery sql(m_db);

    QString strsql = QString("update username set password='%1', auth ='%2' where username = '%3'").
                     arg(info.password).
                     arg(info.aut).
                     arg(info.username);
    return sql.exec(strsql);
}

bool stuSql::addUser(userInfo info)
{
    QSqlQuery sql(m_db);

    QString strsql = QString("insert into username values('%1','%2','%3');").
                     arg(info.username).
                     arg(info.password).
                     arg(info.aut);
    return sql.exec(strsql);
}

bool stuSql::delUser(QString strUserName)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from username where username='%1'").arg(strUserName));
}

void stuSql::closedb()
{
    m_db.close();
}
